package com.example.unique.database

import android.annotation.SuppressLint
import android.content.ContentValues
import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import androidx.core.content.contentValuesOf

class DatabaseJob private constructor(context: Context) : SQLiteOpenHelper(context, "Job.db", null, 1) {
   // 单例模式，确保所有 Fragment 和 Activity 共享同一个 DatabaseJob 实例
   companion object {
       @Volatile
       private var INSTANCE: DatabaseJob? = null

       fun getInstance(context: Context): DatabaseJob {
           return INSTANCE ?: synchronized(this) {
               INSTANCE ?: DatabaseJob(context.applicationContext).also { INSTANCE = it }
           }
       }
   }
    override fun onCreate(db: SQLiteDatabase) {
        val createTable = """
            CREATE TABLE Job (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                club TEXT,     --任务所属社团名
                startTime TEXT,  --任务开始时间
                endTime TEXT,    --任务结束时间
                target TEXT,     --任务打卡地点
                radius REAL,     --任务打卡范围    使用REAL类型存储双精度浮点数时间戳
                jobStatus INTEGER, --任务状态   未发布 ，进行中，已完成，已过期    0 ，1，2，3
                taskName TEXT UNIQUE,  --任务标题  不能重复
                qrCodeUri TEXT --二维码的URI
            )
        """
        db.execSQL(createTable)
    }
//completorSno TEXT UNIQUE,
    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        db.execSQL("DROP TABLE IF EXISTS Job")
        onCreate(db)
    }

    // 检查任务是否存在
    fun isJobExist(club: String): Boolean {
        val db = this.readableDatabase
        val cursor: Cursor = db.rawQuery("SELECT * FROM User WHERE club = ?", arrayOf(club))
        val exists = cursor.count > 0
        cursor.close()
        return exists
    }
    fun getJobID(club:String,taskName:String): Int? {
        val db = this.readableDatabase
        val cursor: Cursor = db.rawQuery("SELECT id FROM Job WHERE club = ? AND taskName = ?", arrayOf(club,taskName))
        var jobID: Int? = null
        if (cursor.moveToFirst()) {
            // 只会有一条记录，因为 taskName 是唯一的
            val idIndex = cursor.getColumnIndex("id")
            if (idIndex >= 0) {
                jobID = cursor.getInt(idIndex)
            }
        }
        cursor.close()
        return jobID
    }
    fun updateStatus(club: String, taskName: String, status: Int) {
        val db = this.writableDatabase
        // 获取任务的 ID
        val id = getJobID(club, taskName)  // 使用之前定义的 getJobID 函数获取 id

        if (id != null) {
            val contentValues = ContentValues()
            contentValues.put("jobStatus", status)
            // 更新任务状态
            db.update("Job", contentValues, "club = ? AND id = ?", arrayOf(club, id.toString()))
        }
    }


    fun insertJob(club:String,start:String,end:String,target:String,radius:Double,status:Int,taskName: String,qrCodeUri:String) {
        val db = this.writableDatabase
        val contentValues = ContentValues()
        contentValues.put("club",club)
        contentValues.put("startTime",start)
        contentValues.put("endTime",end)
        contentValues.put("target",target)
        contentValues.put("radius",radius)
        contentValues.put("jobStatus",status)
        contentValues.put("taskName",taskName)
        contentValues.put("qrCodeUri",qrCodeUri)
        db.insert("Job", null, contentValues)

    }

    //查询某个社团全部的任务
    fun getJobsByClub(club: String): Cursor{
        val db = this.readableDatabase
        val cursor: Cursor = db.rawQuery("SELECT * FROM Job WHERE club = ?", arrayOf(club))
        return cursor
    }
    fun getJobsByClubAndStatus(club: String, status: Int): Cursor {
        val db = this.readableDatabase
        val cursor:Cursor=db.rawQuery("SELECT * FROM Job WHERE club = ? AND jobStatus = ?", arrayOf(club, status.toString()))
        return cursor
    }

    fun getTargetJob(club: String,taskName: String):Cursor{
        val db=this.readableDatabase
        val  cursor:Cursor=db.rawQuery("SELECT * FROM Job WHERE club= ? AND taskName= ?", arrayOf(club,taskName))
        return cursor
    }




}
